﻿//Copyrigh (C) 2021 上海东欣软件工程有限公司 版权所有。
//<author>邢耀光</author>
//<data>2021.1.31</data>

using Core;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;

namespace Dal
{
    /// <summary>
    /// 用户操作类
    /// </summary>
    internal sealed class UserDao: IUserDao
    {
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">用户信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int InsertUser(User user,string connString)
        {
            try
            {
                string format = "INSERT INTO [tbAclUser]([Name],[Description],[IsSystemOnly],[UserType],[DepartmentID]) VALUES (N'{0}',N'{1}',0 ,0,{2})";
                string sql = string.Format(format, user.Name, user.Description, user.DepartmentID);
                return Dbutil.ExecuteNonQuery(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="user">用户信息</param
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int UpdateUser(User user, string connString)
        {
            try
            {
                string format = "UPDATE [tbAclUser] SET [Name] = N'{0}', [Description] = N'{1}',[DepartmentID] = {2} WHERE [ID] = {3}";
                string sql = string.Format(format, user.Name, user.Description, user.DepartmentID,user.ID);
                return Dbutil.ExecuteNonQuery(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id">用户id</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int DeleteUser(long id, string connString)
        {
            try
            {
                string format = "DELETE FROM [tbAclUser] WHERE [ID] ={0} ";
                string sql = string.Format(format, id);
                return Dbutil.ExecuteNonQuery(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }           
        }

        /// <summary>
        /// 删除用户根据部门ID
        /// </summary>
        /// <param name="departmentID">部门id</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>影响行数，1成功，0失败</returns>
        public int DeleteUserByDepartment(long departmentID, string connString)
        {
            try
            {
                string format = "DELETE FROM [tbAclUser] WHERE [DepartmentID] ={0} ";
                string sql = string.Format(format, departmentID);
                return Dbutil.ExecuteNonQuery(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 模糊查询用户
        /// </summary>
        /// <param name="args">用户查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果集</returns>
        public List<User> QueryUser(User args, string connString)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from vwAclUser where 1=1");
                if (args.DepartmentID > 0)
                {
                    sb.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name Like N'%{0}%' ", args.Name);
                }
                if (!string.IsNullOrEmpty(args.Description))
                {
                    sb.AppendFormat(" AND Description Like N'%{0}%' ", args.Description);
                }
                string sql = sb.ToString();
                List<User> users = new List<User>();
                using (IDataReader reader = Dbutil.ExecuteReader(sql, connString))
                {
                    while (reader.Read())
                    {
                        User user = UserReader(reader);
                        users.Add(user);
                    }
                }
                return users;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }           
        }

        /// <summary>
        /// 分页模糊查询用户
        /// </summary>
        /// <param name="args">用户查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="page_size">页数显示大小</param>
        /// <param name="page_index">页数索引</param>
        /// <param name="totalRows">总行数</param>
        /// <returns>查询结果集</returns>
        public DataTable QueryUserPages(User args, string connString,int page_size, int page_index, out int totalRows)
        {
            try
            {
                //获取指定列数的内容
                StringBuilder sqlTotalRows = new StringBuilder();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM (select *,ROW_NUMBER()over(order by id) as rowindex from vwAclUser where 1=1 ");
                sqlTotalRows.Append("select COUNT(*) from vwAclUser where 1=1");
                
                if (args.DepartmentID > 0)
                {
                    sb.AppendFormat(" AND ( DepartmentID = {0} ", args.DepartmentID);
                    sqlTotalRows.AppendFormat(" AND ( DepartmentID = {0} ", args.DepartmentID);
                    SqlConnection conn = new SqlConnection(connString);
                    SqlCommand cmd = new SqlCommand("ProAclGetDescendantDepartments", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int, 32));
                    //把具体的值传给输入参数
                    cmd.Parameters["@DepartmentID"].Value = args.DepartmentID;
                    //执行存储过程
                    SqlDataAdapter myCommand = new SqlDataAdapter();
                    myCommand.SelectCommand = cmd;
                    DataTable dtepartment = new DataTable();
                    myCommand.Fill(dtepartment);
                    foreach (DataRow item in dtepartment.Rows)
                    {
                        sb.AppendFormat(" OR DepartmentID = {0} ", item["ID"].ToString());
                        sqlTotalRows.AppendFormat(" OR DepartmentID = {0} ", item["ID"].ToString());
                    }
                    sb.Append(")");
                    sqlTotalRows.Append(")");
                    //sb.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                    //sqlTotalRows.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name Like N'%{0}%' ", args.Name);
                    sqlTotalRows.AppendFormat(" AND Name Like N'%{0}%' ", args.Name);
                }
                if (!string.IsNullOrEmpty(args.Description))
                {
                    sb.AppendFormat(" AND Description Like N'%{0}%' ", args.Description);
                    sqlTotalRows.AppendFormat(" AND Description Like N'%{0}%' ", args.Description);
                }
                string sql = sqlTotalRows.ToString();
                totalRows = Int32.Parse(Dbutil.GetDataTable(sql, connString, null).Rows[0][0].ToString());
                sb.Append(")as t where 1=1 AND rowindex between  @startindex and @endindex");
                sql = sb.ToString();
                //给参数赋值
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("@startindex", (page_size * (page_index - 1) + 1).ToString());
                dic.Add("@endindex", (page_size * page_index).ToString());

                return Dbutil.GetDataTable(sql, connString, dic);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 分页精确查询用户
        /// </summary>
        /// <param name="args">用户查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="page_size">页数显示大小</param>
        /// <param name="page_index">页数索引</param>
        /// <param name="totalRows">总行数</param>
        /// <returns>查询结果集</returns>
        public DataTable ExactQueryUserPages(User args, string connString, int page_size, int page_index,out int totalRows)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                StringBuilder sqlTotalRows = new StringBuilder();
                sqlTotalRows.Append("select COUNT(*) from vwAclUser where 1=1");
                sb.Append("SELECT * FROM (select *,ROW_NUMBER()over(order by id) as rowindex from vwAclUser)as t where 1=1");
                if (args.DepartmentID > 0)
                {
                    SqlConnection conn = new SqlConnection(connString);
                    SqlCommand cmd = new SqlCommand("ProAclGetDescendantDepartments", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int, 32));
                    //把具体的值传给输入参数
                    cmd.Parameters["@DepartmentID"].Value = args.DepartmentID;
                    //执行存储过程
                    SqlDataAdapter myCommand = new SqlDataAdapter();
                    myCommand.SelectCommand = cmd;
                    DataTable dtepartment = new DataTable();
                    myCommand.Fill(dtepartment);
                    foreach (DataRow item in dtepartment.Rows)
                    {
                        sb.AppendFormat(" AND DepartmentID = {0} ", item["ID"].ToString());
                        sqlTotalRows.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                    }                    
                    //sb.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                    //sqlTotalRows.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name = N'{0}' ", args.Name);
                    sqlTotalRows.AppendFormat(" AND Name = N'{0}' ", args.Name);
                }
                if (!string.IsNullOrEmpty(args.Description))
                {
                    sb.AppendFormat(" AND Description = N'{0}' ", args.Description);
                    sqlTotalRows.AppendFormat(" AND Description = N'{0}' ", args.Description);
                }
                string sql = sqlTotalRows.ToString();
                totalRows = Int32.Parse(Dbutil.GetDataTable(sql, connString, null).Rows[0][0].ToString());
                sb.Append(" AND row_index between  @startindex and @endindex");
                sql = sb.ToString();
                //给参数赋值
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("@startindex", (page_size * (page_index - 1) + 1).ToString());
                dic.Add("@endindex", (page_size * page_index).ToString());
                return Dbutil.GetDataTable(sql, connString, dic);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 精确查询用户
        /// </summary>
        /// <param name="args">用户查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果集</returns>
        public List<User> ExactQueryUser(User args, string connString)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from vwAclUser where 1=1");
                if (args.DepartmentID > 0)
                {
                    sb.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name = N'{0}' ", args.Name);
                }
                if (!string.IsNullOrEmpty(args.Description))
                {
                    sb.AppendFormat(" AND Description = N'{0}' ", args.Description);
                }
                string sql = sb.ToString();
                List<User> users = new List<User>();
                using (IDataReader reader = Dbutil.ExecuteReader(sql, connString))
                {
                    while (reader.Read())
                    {
                        User user = UserReader(reader);
                        users.Add(user);
                    }
                }
                return users;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 模糊查询用户
        /// </summary>
        /// <param name="args">用户查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果集</returns>
        public DataTable QueryUserDT(User args, string connString)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from vwAclUser where 1=1");
                if (args.DepartmentID > 0)
                {
                    sb.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name Like N'%{0}%' ", args.Name);
                }
                if (!string.IsNullOrEmpty(args.Description))
                {
                    sb.AppendFormat(" AND Description Like N'%{0}%' ", args.Description);
                }
                string sql = sb.ToString();               
                return Dbutil.ExecuteAdapter(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }


        /// <summary>
        /// 精确查询用户
        /// </summary>
        /// <param name="args">用户查询参数信息</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果集</returns>
        public DataTable ExactQueryUserDT(User args, string connString)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from vwAclUser where 1=1");
                if (args.DepartmentID > 0)
                {
                    sb.AppendFormat(" AND DepartmentID = {0} ", args.DepartmentID);
                }
                if (!string.IsNullOrEmpty(args.Name))
                {
                    sb.AppendFormat(" AND Name = N'{0}' ", args.Name);
                }
                if (!string.IsNullOrEmpty(args.Description))
                {
                    sb.AppendFormat(" AND Description = N'{0}' ", args.Description);
                }
                string sql = sb.ToString();

                return Dbutil.ExecuteAdapter(sql, connString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 使用SqlBulkCopy批量插入
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="usrsDT">批量插入数据</param>
        /// <returns></returns>
        public bool ImportUser(string connString, DataTable usrsDT)
        {            
            try
            {
                bool isSucc = false;
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();//开启事务                
                //在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
                bulkCopy.DestinationTableName = "dbo.tbAclUser";//代表要插入数据的表名
                foreach (DataColumn dc in usrsDT.Columns)  //传入上述table
                {
                    bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将table中的列与数据库表这的列一一对应
                }
                try
                {   bulkCopy.WriteToServer(usrsDT);
                    tran.Commit();
                    isSucc = true;
                }
                catch (Exception)
                {
                    tran.Rollback();
                    throw;
                }
                finally
                {
                    bulkCopy.Close();
                    conn.Close();
                }
                return isSucc;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 根据用户名称和所属部门id判断是否已经存在当前用户
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="userName">用户名称</param>
        /// <param name="departmentID">部门名称</param>
        /// <returns>是否存在</returns>
        public bool ExistUser(string connString, string userName, int departmentID)
        {
            try
            {
                User args = new User();
                args.Name = userName;
                args.DepartmentID = departmentID;
                List<User> users = ExactQueryUser(args, connString);
                return users.Count > 0;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="reader">读取的用户信息结果集</param>
        /// <returns>用户信息</returns>
        private static User UserReader(IDataReader reader)
        {
            try
            {
                User user = new User();
                user.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                user.DepartmentID = reader.GetInt32(reader.GetOrdinal("DepartmentID"));
                user.DepartmentName = reader.GetString(reader.GetOrdinal("DepartmentName"));
                user.Name = reader.GetString(reader.GetOrdinal("Name"));
                user.Description = reader.GetString(reader.GetOrdinal("Description"));
                user.IsSystemOnly = reader.GetBoolean(reader.GetOrdinal("IsSystemOnly"));
                user.UserType = reader.GetByte(reader.GetOrdinal("UserType"));
                user.Creator = reader.GetString(reader.GetOrdinal("Creator"));
                user.CreateTime = reader.GetDateTime(reader.GetOrdinal("CreateTime"));
                user.Modifier = reader.GetString(reader.GetOrdinal("Modifier"));
                user.ModifyTime = reader.GetDateTime(reader.GetOrdinal("ModifyTime"));
                return user;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }
    }
}
